package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DAO.LernbgrpDAO;
import DAO.LehrerDAO;


import Model.Klasse;
import Model.Schueler;

public class KlasseDAO {
	
	private LehrerDAO lehrerDAO = new LehrerDAO();
	private LernbgrpDAO lernbgrpDAO = new LernbgrpDAO();
	ArrayList<Klasse> liste=new ArrayList<Klasse>();

	
	public Klasse getKlasse(int id)
	{
		Klasse klasse=null;
		Connection conn=ConnectionProvider.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		String sql="select * from lehrer where lehrerid=?";
        try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			rs=statement.executeQuery();
			
			if (rs.next())
			{
				klasse=new Klasse();
				klasse.setId(id);
				klasse.setSchuljahr(rs.getString("schuljahr"));
				klasse.setKbez(rs.getString("kbez"));
				klasse.setLehrer(lehrerDAO.getLehrer(rs.getInt("lehrerID")));
				klasse.setLernbgrp(lernbgrpDAO.getLernbgrp(rs.getInt("lbgruppeID")));
			}
//	lernbuero.setLernbgrp(lernbgrpDAO.getLernbgrp(rs.getInt("lbgruppeID")));

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				rs.close();
				statement.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return klasse;
	}
	
	public void update(Klasse klasse)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="update klasse set schuljahr=?, kbez=?, lehrerID=?, lbgruppeID=? where klasseID=?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, klasse.getSchuljahr());
			statement.setString(2, klasse.getKbez());
			statement.setInt(3, klasse.getLehrer().getId());
			statement.setInt(4, klasse.getLernbgrp().getId());
			statement.setInt(6, klasse.getId());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void anlegen(Klasse klasse)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="insert into klasse VALUES (?, ?, ?, ?, ?)";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, klasse.getId());
			statement.setString(2, klasse.getSchuljahr());
			statement.setString(3, klasse.getKbez());
			statement.setInt(4, klasse.getLehrer().getId());
			statement.setInt(5, klasse.getLernbgrp().getId());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void delete(int id)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="delete from klasse where klasseID= ?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			statement.executeUpdate();
        } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
        finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
//	public ArrayList<Klasse> liste()
//	{
//		Klasse k = new Klasse();
//
////		Connection conn=ConnectionProvider.getConnection();
////		PreparedStatement statement=null;
////		ResultSet rs=null;
////		String sql="select * from lehrer where lehrerid=?";
//		
//		// count 
//		
//		//array erzeugen
//		
//		// zweite abfrage der klassenbezeichnungen
//		
//		// in das array packen
//		
////		return null;
////	}
//	
//	Klasse[] klassen = new Klasse[liste.size()];
//	for(int i=0; i<liste.size(); i++)
//	{
//		klassen[i]=k.get;	
//	}	
//	return liste;
//	}

	public Klasse[] getKlassenListe()
	{
	Connection conn = ConnectionProvider.getConnection();
	PreparedStatement statement=null;
	ResultSet rs=null;
	ArrayList<Klasse> k=new ArrayList<Klasse>();
	System.out.println((k));


	String sql = "select * from klasse";
	try{
	statement = conn.prepareStatement(sql);
	rs=statement.executeQuery();
	while(rs.next())
	{


	k.add(this.getKlasse(rs.getInt("klasseID")));
	} 




	}catch(SQLException e){
	//TODO Auto-generated catch block
	e.printStackTrace();
	}
	finally {
	try{
	conn.close();
	}catch(SQLException e){
	//TODO Auto-generated catch block
	e.printStackTrace();
	}
	}


	Klasse[] klassen = new Klasse[k.size()];
	for(int i=0; i<k.size(); i++)
	{
	klassen[i]=k.get(i); 
	} 
	return klassen;
	}
	
}
